<?php

class Db_Joins_LevelJoins
{
    var $cateogry;
	var $keyword;
	var $level_id;
	var $channel_id;
	
	function __construct() {
		$this->db = Zend_Registry::get("db");
	}
	
	function GetHomePageProducts()
	{
		$select = $this->db->select();
		$select->from("level3", array("level1_id"));
		$select->joinLeft("level1", "level1.id=level3.level1_id", array("title", "image_url", "joined_members"));
		$select->where("level1.status = ?", 1);
		$select->where("level1.channel_id = ?", $this->channel_id);
		
		if($this->keyword)
		{
			$select->where("level1.title like '%".$this->keyword."%' or level3.banner_text like '%".$this->keyword."%' or level3.slogan like '%".$this->keyword."%'");
		}
		
		if($this->cateogry)
		{
			$select->where("level3.category_id =?", $this->cateogry);
		}
		
		$select->group("level3.level1_id");
		$select->order("level1.ctime DESC");
		$select->limit(50);
		
		$result = $this->db->fetchAll($select);
		
		return $result;
	}
	
	function GetLevel1PageProducts()
	{
		$select = $this->db->select();
		$select->from("level3", array("level2_id"));
		$select->joinLeft("level2", "level2.id=level3.level2_id", array("image_url"));
		
		if($this->keyword)
		{
			$select->where("level3.banner_text like '%".$this->keyword."%' or level3.slogan like '%".$this->keyword."%'");
		}
		
		if($this->cateogry)
		{
			$select->where("level3.category_id =?", $this->cateogry);
		}
		
		$select->where("level2.level1_id = ?", $this->level_id);
		$select->where("level2.status = ?", 1);
		
		$select->group("level3.level2_id");
		$select->order("level3.id ASC");
		$select->limit(50);
		
		$result = $this->db->fetchAll($select);
		
		return $result;
	}
	
	function GetLevel2PageProducts()
	{
		$select = $this->db->select();
		$select->from("level3", array("id", "level1_id", "banner_url", "banner_text"));
		
		$select->where("level2_id = ?", $this->level_id);
		$select->where("status = ?", 1);
		
		$select->order("id DESC");
		$select->limit(100);
		
		$result = $this->db->fetchAll($select);
		
		return $result;
	}
	
	function GetLevel3Details()
	{
		$select = $this->db->select();
		$select->from("level3", "*");
		
		$select->where("id = ?", $this->level_id);
		
		$result = $this->db->fetchRow($select);
		
		return $result;
	}
	
	function GetLevel3Status()
	{
		$select = $this->db->select();
		$select->from("level3", "status as s3");
		$select->joinLeft("level2", "level2.id=level3.level2_id", "status as s2");
		$select->joinLeft("level1", "level1.id=level3.level1_id", "status as s1");
		$select->where("level3.id = ?", $this->level_id);
		
		$row = $this->db->fetchRow($select);
		
		$result = 0;
		
		if($row)
		{
			if($row['s1'] && $row['s2'] && $row['s3'])
			{
				$result = 1;
			}
		}
		
		return $result;
	}
}
